﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Public;

namespace DAL
{
    public class NguoiDungQHDAL
    {
        public static bool KiemTraDangNhap(string TenDangNhap, string MatKhau, ref NguoiDungQHPublic user_login, ref string message)
        {
            // Common.db.Connection.Open();
            message = "";
            try
            {
                if (!Common.db.DatabaseExists()) //if (Common.db.Connection.State == ConnectionState.Closed && Common.db.CommandTimeout == null) // !Common.db.DatabaseExists())
                {
                    message = "Kết nối CSDL thất bại!";
                    return false;
                }
            }
            catch
            {
                message = "Kết nối CSDL thất bại!";
                return false;
            }
            string chuoi = "select MaND,HoTen,TenDangNhap,MaCV,MatKhau from NguoiDung where TenDangNhap=" + "'" + TenDangNhap + "'" + " and MatKhau=" + "'" + MatKhau + "'";
            var nguoidung = Common.db.ExecuteStoreQuery<NguoiDungQHPublic>(@chuoi);
            user_login = (NguoiDungQHPublic)nguoidung.FirstOrDefault();

            if (user_login != null)
            {
                return true;
            }
            message = "Sai tên đăng nhập hay mật khẩu!";
            return false;
        }
        public static List<NguoiDungQHPublic> LoadToanBoNguoiDung()
        {
            var nd = Common.db.ExecuteStoreQuery<NguoiDungQHPublic>(@"select MaND,HoTen,TenDangNhap,MatKhau,MaCV,NgaySinh,TinhTrang,ISNULL(MaNK,0) as MaNK, ThoiGianBatDau, ThoiGianKetThuc from NguoiDung");
            return nd.ToList();
        }
        public static List<NguoiDungQHPublic> LoadNguoiDungHienTai()
        {
            var nd = Common.db.ExecuteStoreQuery<NguoiDungQHPublic>(@"select MaND,HoTen,TenDangNhap,MatKhau,ChucVu from NguoiDung where TrangThai=1");
            return nd.ToList();
        }
        public static void InsertNguoiDung(NguoiDungQHPublic _ndqh)
        {
            NguoiDung ndqh = new NguoiDung();

            ndqh.MaND = _ndqh.MaND;
            ndqh.MaNK = null;
            ndqh.TenDangNhap = _ndqh.TenDangNhap;
            ndqh.MatKhau = _ndqh.MatKhau;
            ndqh.HoTen = _ndqh.HoTen;
            ndqh.NgaySinh = _ndqh.NgaySinh;
            ndqh.ThoiGianBatDau = _ndqh.ThoiGianBatDau;
            ndqh.ThoiGianKetThuc = _ndqh.ThoiGianKetThuc;
            //nd.MaPX = _nd.MaPX;
            ndqh.MaCV = _ndqh.MaCV;
            ndqh.TinhTrang = _ndqh.TinhTrang;
            //nd.SoDienThoai = _nd.SoDienThoai;
            Common.db.AddToNguoiDungs(ndqh);
            Common.db.SaveChanges();
        }
        public static void InsertNguoiDungNK(NguoiDungQHPublic _ndqh)
        {
            NguoiDung ndqh = new NguoiDung();

            ndqh.MaND = _ndqh.MaND;
            ndqh.MaNK = _ndqh.MaNK;
            ndqh.TenDangNhap = _ndqh.TenDangNhap;
            ndqh.MatKhau = _ndqh.MatKhau;
            ndqh.ThoiGianBatDau = _ndqh.ThoiGianBatDau;
            ndqh.ThoiGianKetThuc = _ndqh.ThoiGianKetThuc;
            //nd.MaPX = _nd.MaPX;
            ndqh.MaCV = _ndqh.MaCV;
            ndqh.TinhTrang = _ndqh.TinhTrang;
            //nd.SoDienThoai = _nd.SoDienThoai;
            Common.db.AddToNguoiDungs(ndqh);
            Common.db.SaveChanges();
        }
        public static void DeleteNguoiDung(int MaND)
        {
            var nd = Common.db.ExecuteStoreQuery<NguoiDungQHPublic>(@"delete from NguoiDung where MaND=" + MaND);

        }
        //public static string LoadHoTen(string TenDangNhap)
        //{
        //    var nd = Common.db.ExecuteStoreQuery<string>(@"select HoTen from NguoiDung where TenDangNhap=" + TenDangNhap);
        //    return nd.FirstOrDefault();
        //}
        public static NguoiDungQHPublic LoadNguoiDungByMaND(int MaND)
        {
            var nd = Common.db.ExecuteStoreQuery<NguoiDungQHPublic>(@"select MaND,HoTen,TenDangNhap,MaCV,MatKhau,NgaySinh,TinhTrang,ISNULL(MaNK,0) as MaNK,ThoiGianBatDau,ThoiGianKetThuc from NguoiDung where MaND=" + MaND);
            return nd.FirstOrDefault();
        }
        public static void UpdateTinhTrang(int MaND, bool TinhTrang)
        {
            var nd = Common.db.ExecuteStoreQuery<NguoiDungQHPublic>(@"update NguoiDung set TinhTrang=" + "'" + TinhTrang + "'" + "where MaND=" + MaND);
            Common.db.SaveChanges();
        }
        public static List<NguoiDungQHPublic> LoadNguoiCap()
        {
            var nd = Common.db.ExecuteStoreQuery<NguoiDungQHPublic>(@"select MaND,HoTen from NguoiDungQH where MaCV=01 or MaCV=02");
            return nd.ToList();
        }
        public static List<NguoiDungQHPublic> LoadCanBoHoTich()
        {
            var nd = Common.db.ExecuteStoreQuery<NguoiDungQHPublic>(@"select MaND,HoTen from NguoiDungQH where MaCV=06");
            return nd.ToList();
        }
        public static List<NguoiDungQHPublic> LoadChuTich()
        {
            var nd = Common.db.ExecuteStoreQuery<NguoiDungQHPublic>(@"select MaND,HoTen from NguoiDungQH where MaCV=04 or MaCV=05");
            return nd.ToList();
        }
        public static int LoadMaND()
        {
            var nd = Common.db.ExecuteStoreQuery<int>(@"select ISNULL(Max(MaND),0) from NguoiDungQH");
            return nd.FirstOrDefault();
        }
        public static void UpdateNguoiDung(int MaND, string TenDN, string HoTen, string MatKhau, DateTime NgaySinh, DateTime BD, DateTime KT, bool TinhTrang, byte ChucVu)
        {
            string chuoi = "Update NguoiDung set TenDangNhap='" + TenDN + "', HoTen=N'" + HoTen + "', MatKhau='" + MatKhau + "', NgaySinh='" + NgaySinh + "', ThoiGianBatDau='" + BD + "', ThoiGianKetThuc='" + KT + "',TinhTrang='"+TinhTrang+"',MaCV="+ChucVu+" where MaND=" + MaND;
            var nd = Common.db.ExecuteStoreQuery<NguoiDungPublic>(chuoi);
            Common.db.SaveChanges();
        }
    }
}
